﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using VideoNetApplication.Common.Helper;

namespace VideoNetApplication.Common.Dao
{
    public class CourseDao
    {
        public List<V_course> GetAllCoursebySchoolid(int schoolId)
        {
            using (var edm=new EVDBEntities())
            {
                return edm.V_course.Where(s => s.SchoolID == schoolId).ToList();
            }
        }

        public List<V_course> GetCourse(int schoolId, int startIndex, int maxRows, out int recordCount)
        {
            using (var edm = new EVDBEntities())
            {
                
                recordCount= edm.V_course.Where(s => s.SchoolID == schoolId).Count();
                return edm.V_course.Where(s => s.SchoolID == schoolId).OrderByDescending(s=>s.ID).Skip(startIndex-1).Take(maxRows).ToList();
            }
        }

        public int ChkExsit(int vsid)
        {
            using (var edm = new EVDBEntities())
            {

                return edm.VideoFormatNeed_Table.Where(s => s.Status !=3&&s.VSID==vsid).Count();
            }
           
            //return  
            //string sql = "SELECT count(*) FROM VideoFormatNeed_Table WHERE Status<>3 AND VSID=" + vsid;
      
        }



        /// <summary>
        /// 获取学生的课程表列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="schoolId"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetUserCourseList(int userId, int schoolId, string strWhere, string orderByField)
        {
            var strSql = new StringBuilder();
            strSql.Append("select    c.ID as CourseID, c.VC_name,b.ID,b.VCL_startDate,b.VCL_startTime,b.VCL_EndDate,b.VCL_EndTime,b.VCL_desc,b.teacherID,b.RoomID,(Select VR_roomName From  v_room Where Id=b.RoomID) as RoomName, a.UserID,a.SchoolID,a.TrainClassID,(Select Vtc_name From v_trainClass Where ID=a.TrainClassID ) as Vtc_name   ");
            strSql.AppendLine("  FROM V_courselistDest a  join V_courselist  b on  a.TrainClassID=b.trainClassID    ");
            strSql.AppendLine(" join V_course  c  on b.courseID=c.ID   ");
            strSql.AppendFormat(" where  a.UserID={0} and a.SchoolID={1}  ",userId,schoolId);
            
            if (strWhere != "")
            {
                strSql.Append(strWhere);
            }
            if (orderByField != "")
            {

                strSql.Append("  order by  " + orderByField);
            }
            return  DBHelper.ExecuteDataSet(strSql.ToString());

        }


        public  DataSet GetCourseDetailByDestId(int schoolId,int courseId)
        {
            string sql = " select top 1 vc_name,vt_realname,vc_desc   FROM V_courselist a join v_course b on a.courseID=b.ID JOIN dbo.V_teacher t ON a.teacherID=t.id  where a.SchoolID="
                +schoolId+" and a.ID="+courseId;
            return DBHelper.ExecuteDataSet(sql);
        }


        public  V_course GetCourseById(int schoolId,int courseid)
        {
            using (EVDBEntities edm=new EVDBEntities())
            {
                return edm.V_course.FirstOrDefault(s => s.SchoolID == schoolId && s.ID == courseid);
            }
        }

    }
}
